package com.chj.mybatis.capt2;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.chj.mybatis.entity.EmailSexBean;
import com.chj.mybatis.entity.TUser;
import com.chj.mybatis.mapper.TUserMapper;
import com.chj.mybatis.mapper.TUserMapperExt;

public class MybatisDemo {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 1.读取mybatis配置文件创SqlSessionFactory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        inputStream.close();
    }

    /**
     * test auto mapping
     */
    @Test
    public void testAutoMapping() {
        // 2. achieve sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. achieve mapper
        TUserMapperExt extmapper = sqlSession.getMapper(TUserMapperExt.class);
        // 4. execute query and return result
        TUser user = extmapper.selectByPrimaryKeyAutoMapper(1);
        System.out.println(user.toString());
    }

    /**
     * more param query
     */
    @Test
    public void testMoreParamQuery() {
        // 2. achieve sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3. achieve mapper
        TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);

        String email = "qq.com";
        Byte sex = 1;

        // method 1: use map as param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("email", email);
        params.put("sex", sex);
        List<TUser> list1 = mapperExt.selectByEmailAndSex1(params);
        System.out.println("list1===" + list1.size());

        // method 2: use param directly
        List<TUser> list2 = mapperExt.selectByEmailAndSex2(email, sex);
        System.out.println("list2===" + list2.size());

        // method 3: use map as param
        EmailSexBean esb = new EmailSexBean();
        esb.setEmail(email);
        esb.setSex(sex);
        List<TUser> list3 = mapperExt.selectByEmailAndSex3(esb);
        System.out.println("list3===" + list3.size());

    }

    /**
     * 测试插入数据自动生成id
     *
     * @throws IOException
     */
    @Test
    public void testInsertGenerateId1() throws IOException {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
        // 4.执行查询语句并返回结果
        TUser user1 = new TUser();
        user1.setUserName("test1");
        user1.setRealName("realname1");
        user1.setEmail("myemail1");
        mapper.insert1(user1);
        sqlSession.commit();
        System.out.println(user1.getId());
    }

    /**
     * 测试插入数据自动生成id
     *
     * @throws IOException
     */
    @Test
    public void testInsertGenerateId2() throws IOException {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
        // 4.执行查询语句并返回结果
        TUser user2 = new TUser();
        user2.setUserName("test2");
        user2.setRealName("realname2");
        user2.setEmail("myemai2l");
        mapper.insert2(user2);
        sqlSession.commit();
        System.out.println(user2.getId());
    }


    @Test
    // 注解测试
    public void testAnno() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);

        List<TUser> list = mapperExt.selectByUserIdAnnotation(1);
        System.out.println(list.size());

        List<TUser> listAll = mapperExt.selectAllAnnotation();
        System.out.println(listAll.size());

        // 4.执行查询语句并返回结果
        TUser user = new TUser();
        user.setUserName("lisi");
        user.setRealName("ssdsfdf");
        user.setEmail("sdgsg");
        mapperExt.insertAnnotation(user);
        sqlSession.commit();
        System.out.println(user.getId());
    }

    /**
     * 参数#和参数$区别测试(动态sql 入门)
     * 注意：两者区别在于传入的参数 是否加了 ‘’引号，#是有占位符 ？ 的 $是直接传入的参数
     */
    @Test
    public void testSymbol() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);

        String inCol = "id, user_name, real_name, sex, mobile, email, note";
        String tableName = "t_user";
        String userName = "'xxx' or 1=1";
        String orderStr = "sex,user_name";

        List<TUser> list = mapperExt.selectBySymbol(tableName, inCol, orderStr, userName);
        System.out.println(list.size());

    }

    //--------------------------------动态sql---------------------------------------

    /**
     * if用于select，并与where配合
     */
    @Test
    public void testSelectIfOper() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);

        String email = "qq.com";
        Byte sex = 1;
        // 如果设置为null sql会报错： where a.email like CONCAT('%', ?, '%') and 结尾and 没有删除

        // 不用<where> 查询
        List<TUser> list = mapper.selectIfOperate(email, sex);
        System.out.println(list.size());
        for (TUser user : list) {
            System.out.println(user.toString());
        }
        // 使用<where> 查询
        String email2 = "lison";
        Byte sex2 = null;
        List<TUser> list2 = mapper.selectIfandWhereOper(email2, sex2);
        System.out.println(list2.size());
        for (TUser user : list2) {
            System.out.println(user.toString());
        }

    }

    /**
     * if用于update，并与set配合
     * 使用<set> 可以删除sql多余的 ‘，’
     */
    @Test
    public void testUpdateIfOper() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);

        TUser user = new TUser();
        user.setId(3);
        user.setUserName("cindy");
        user.setRealName("王美丽");
        user.setEmail("sss@163.com");
        user.setMobile("1235764532");
        user.setSex((byte) 2);

        //  update t_user set user_name = ?, real_name = ?, sex = ?, mobile = ?, email = ? where id = ?
//		System.out.println(mapper.updateIfAndSetOper(user));

        user.setNote("cindy's note");
        //  update t_user set user_name = ?, real_name = ?, sex = ?, mobile = ?, email = ?, where id = ?
        System.out.println(mapper.updateIfOperate(user));

        sqlSession.commit();

    }

    /**
     * if用于insert，并与trim配合
     */
    @Test
    public void testInsertIfOper() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);

        TUser user = new TUser();
        user.setUserName("hankin");
        user.setRealName("chnehuajing");
        user.setEmail("1234@163.com");
        user.setMobile("18695988747");
        user.setSex((byte) 1);
//		user.setNote("hankin's note");
        // insert into t_user ( user_name, real_name, sex, mobile, email, ) values( ?, ?, ?, ?, ?, )
        System.out.println(mapper.insertIfOperate(user));
//		System.out.println(mapper.insertSelective(user));
        sqlSession.commit();
    }


}
